﻿Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports Microsoft.Reporting.WebForms
Imports log4net

Partial Class Report_RP10
    Inherits System.Web.UI.Page

    Public ScriptText As String
    Dim Report As Object
    Private Shared logger As ILog = LogManager.GetLogger("Report_RP10")

    Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init

        'If Session("fundcenterS") = "" Then
        '    Session("fundcenterS") = "0011"
        '    Session("fundcenterE") = "0012" 

        'End If

        'If Session("users") = "" Then
        '    Session("users") = "wassana"
        'End If
        'Session("year") = 2556
        'Session("version") = 2


        Getdata()


    End Sub


    Function Getdata() As Decimal


        Dim adapter As New Data_Report2TableAdapters.RP10TableAdapter
        Dim sql As String
        Dim dt As New Data_Report2.RP10DataTable

        sql += "SELECT BudgetDetails.[Id],BudgetDetails.[PlanYear],BudgetDetails.[Version],BudgetDetails.[Amount],BudgetDetails.[ActivityType_PlanYear]"
        sql += ",BudgetDetails.[ActivityType_ActivityTypeCode],BudgetDetails.[Activity_ActivityCode],BudgetDetails.[Activity_PlanYear],Activities.Description AS ActivityName"
        sql += ",BudgetDetails.[Product_ProductCode],BudgetDetails.[Product_PlanYear],BudgetDetails.[FundCenter_FundCenterCode] as funcenter_code,BudgetDetails.[FundCenter_PlanYear]"
        sql += ",Fundcenters.Description AS funcenter_name,BudgetDetails.[Project_ProjectCode],BudgetDetails.[Project_PlanYear],BudgetDetails.[Project_FundCenterCode]"
        sql += ",Projects.Description AS ProjectName,BudgetDetails.[CmmtDetail_Id],BudgetDetails.[Fund_FundCode],BudgetDetails.[Fund_PlanYear]"
        sql += ",BudgetDetails.[Cmmt_CmmtCode] as cmmt_Code,BudgetDetails.[Cmmt_PlanYear]"

        sql += ",(SELECT CmmtHierarchies.Description FROM CmmtHierarchies WHERE CmmtHierarchies.Id = ( SELECT  [ParentCmmtHierarchy_Id] FROM CmmtHierarchies WHERE CmmtHierarchies.Id = "
        sql += " ( SELECT  [ParentCmmtHierarchy_Id]  FROM CmmtHierarchies WHERE [Cmmt_CmmtCode] = BudgetDetails.[Cmmt_CmmtCode]  AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear]"
        sql += " and CmmtGroup=1) "
        sql += " AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear]) AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear]) AS CmmtParent1"

        sql += " ,(SELECT CmmtHierarchies.Description"
        sql += " FROM CmmtHierarchies WHERE CmmtHierarchies.Id = ( SELECT  [ParentCmmtHierarchy_Id] FROM CmmtHierarchies WHERE [Cmmt_CmmtCode] = BudgetDetails.[Cmmt_CmmtCode]"
        sql += " AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear] "
        sql += " and CmmtGroup=2)"
        sql += " AND [PlanYear] = BudgetDetails.[Cmmt_PlanYear]) AS CmmtParent2"

        sql += ",Cmmts.Description as cmmt_name ,Budget12.[Id] as B12ID ,Budget12.[Amount1],Budget12.[Amount2],Budget12.[Amount3] ,Budget12.[Amount4],Budget12.[Amount5]"
        sql += ",Budget12.[Amount6],Budget12.[Amount7],Budget12.[Amount8],Budget12.[Amount9],Budget12.[Amount10],Budget12.[Amount11],Budget12.[Amount12]"
        sql += ",CASE ISNULL(BudgetDetails.[Project_ProjectCode],'') WHEN '' THEN 'งบประมาณประจำ' ELSE "
        sql += " CASE LEFT(ISNULL(BudgetDetails.[Project_ProjectCode],''),1) WHEN 'P' THEN 'งบประมาณโครงการ' ELSE 'งบประมาณสนับสนุน' END END AS BudgetType"
        sql += " FROM BudgetDetails INNER JOIN Budget12 ON BudgetDetails.Id = Budget12.[BudgetDetail_Id]"
        sql += " INNER JOIN Cmmts ON BudgetDetails.[Cmmt_CmmtCode]=Cmmts.[CmmtCode] and BudgetDetails.[PlanYear]=Cmmts.[PlanYear]"
        sql += " INNER JOIN FundCenters ON BudgetDetails.[FundCenter_FundCenterCode] = Fundcenters.FundCenterCode "
        sql += " AND BudgetDetails.[FundCenter_PlanYear] = FundCenters.PlanYear"
        sql += " LEFT OUTER JOIN Activities ON BudgetDetails.[Activity_ActivityCode] = Activities.ActivityCode"
        sql += " AND BudgetDetails.[Activity_PlanYear] = Activities.PlanYear LEFT OUTER JOIN Projects ON BudgetDetails.[Project_ProjectCode] = Projects.ProjectCode"
        sql += " AND BudgetDetails.[Project_PlanYear] = Projects.PlanYear  AND BudgetDetails.[Project_FundCenterCode] = Projects.FundCenter_FundCenterCode"


        sql += " where  BudgetDetails.PlanYear  = " & Session("year") & " and BudgetDetails.Version = '" & Session("version") & "'"
        If Session("FCTRmode") = "1" Then
            sql += " and  BudgetDetails.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        ElseIf Session("FCTRmode") = "2" Then
            sql += " and  BudgetDetails.FundCenter_FundCenterCode IN (" & Session("FundcenterAll") & ") "
        End If


        If Session("CmmtMode") = "1" Then
            If Val(Session("CmmtCodeS")) <> 0 And Val(Session("CmmtCodeE")) <> 0 Then
                sql += " and  Cmmts.CmmtCode  between " & Session("CmmtCodeS") & " and " & Session("CmmtCodeE")
            End If
            If Val(Session("CmmtCodeS")) <> 0 And Val(Session("CmmtCodeE")) = 0 Then
                sql += " and  Cmmts.CmmtCode = " & Session("CmmtCodeS")
            End If
            If Val(Session("CmmtCodeS")) = 0 And Val(Session("CmmtCodeE")) <> 0 Then
                sql += " and  Cmmts.CmmtCode = " & Session("CmmtCodeE")
            End If
            If Val(Session("CmmtCodeS")) = 0 And Val(Session("CmmtCodeE")) = 0 Then
                sql += " and  Cmmts.CmmtCode  between " & "'00000000'" & " and " & "'99999999'"
            End If
        ElseIf Session("CmmtMode") = "2" Then
            If Session("CmmtParent") <> "" And Session("CmmtParent") <> "0" Then
                sql += "and BudgetDetails.Cmmt_CmmtCode in (select cmmt_cmmtcode from  cmmthierarchies where parentcmmthierarchy_id in (select id from cmmthierarchies "
                sql += " where id = " & Session("CmmtParent") & " or parentcmmthierarchy_id = " & Session("CmmtParent") & ")"
                sql += " or id in (select id from cmmthierarchies "
                sql += " where id = " & Session("CmmtParent") & " or parentcmmthierarchy_id = " & Session("CmmtParent") & ")"
                sql += ")"
            End If
        End If



        'Response.Write(sql)
        ' Exit Function
        adapter.GetDataByFilter(dt, sql)



        'Return 1
        'Exit Function
        dt.TableName = "R10DT"
        Dim das As New DataSet
        das.DataSetName = "R01DS"
        das.Tables.Add(dt)
        ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local
        'ReportViewer1.LocalReport.ReportPath = System.Environment.CurrentDirectory & "Report\R01.rdlc"
        ReportViewer1.LocalReport.DataSources.Clear()
        ReportViewer1.LocalReport.DataSources.Add(New Microsoft.Reporting.WebForms.ReportDataSource("DataSet1", das.Tables("R10DT")))
        ReportViewer1.DocumentMapCollapsed = True

        Dim UserName As String = Session("users")
        Dim years As String = Session("year")
        Dim version As String = Session("version")
        Dim users As String = Session("users")

        Dim p1, p2, p3 As ReportParameter
        p1 = New ReportParameter("year", years)
        p2 = New ReportParameter("version", version)
        p3 = New ReportParameter("username", UserName)

        ReportViewer1.LocalReport.SetParameters(p1)
        ReportViewer1.LocalReport.SetParameters(p2)
        ReportViewer1.LocalReport.SetParameters(p3)


        ReportViewer1.LocalReport.Refresh()


        'Response.Write(dt.Rows.Count & sql)
        Session("count") = dt.Rows.Count



        Return dt.Rows.Count

    End Function


    Public Function nPageNumber() As String
        Dim str As String
        str = Me.Report.Globals!PageNumber.ToString()
        Return str
    End Function

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Session("users") Is Nothing Then
            ScriptText = "<script> wclose();</script>"
        End If
    End Sub
End Class
